package com.bringway.global.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.BaseJdbcDao;
import com.bringway.framework.domain.SysAttechment;
import com.bringway.framework.domain.SysBizAtt;
import com.bringway.global.dao.SysAttechmentDao;

import net.sf.json.JSONObject;

@Repository
public class SysAttechmentDaoImpl extends BaseJdbcDao implements SysAttechmentDao{

	private class SysAttMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			SysAttechment sysAttechment = new SysAttechment();
			sysAttechment.setContextType(rs.getString("CONTENT_TYPE"));
			sysAttechment.setFileName(rs.getString("FILE_NAME"));
			sysAttechment.setId(new Long(rs.getLong("ID")));
			sysAttechment.setSize(rs.getInt("FILE_SIZE"));
			sysAttechment.setFtpPath(rs.getString("FTP_PATH"));
			sysAttechment.setUploadTime(rs.getTimestamp("UPLOAD_TIME"));
			sysAttechment.setUploadUserId(rs.getString("UPLOAD_USERID"));
			sysAttechment.setUploadUserName(rs.getString("UPLOAD_USERNAME"));
			return sysAttechment;
		}
	}
	
	@Override
	public Long createSysAttechment(SysAttechment sysAttechment) {
		final StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO T_FM_SYS_ATTECHMENT(UPLOAD_USERID,UPLOAD_USERNAME,UPLOAD_TIME ");
		sql.append(" ,CONTENT_TYPE,FILE_NAME,FTP_PATH,FILE_SIZE) ");
		sql.append(" VALUES(:UPLOAD_USERID,:UPLOAD_USERNAME,:UPLOAD_TIME ");
		sql.append(" ,:CONTENT_TYPE,:FILE_NAME,:FTP_PATH,:FILE_SIZE )");
		KeyHolder keyHolder = new GeneratedKeyHolder();
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("UPLOAD_USERID", sysAttechment.getUploadUserId())
                .addValue("UPLOAD_USERNAME", sysAttechment.getUploadUserName())
                .addValue("UPLOAD_TIME", sysAttechment.getUploadTime())
                .addValue("CONTENT_TYPE", sysAttechment.getContextType())
                .addValue("FILE_NAME", sysAttechment.getFileName())
                .addValue("FTP_PATH", sysAttechment.getFtpPath())
				.addValue("FILE_SIZE", sysAttechment.getSize());
		this.getNamedParameterJdbcTemplate().update(sql.toString(), parameters, keyHolder);
		return keyHolder.getKey().longValue();
	}

	@Override
	public Collection listAllAttByBizId(String bizId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT T2.ID,T2.FTP_PATH,T2.CONTENT_TYPE,T2.FILE_NAME,T2.FILE_SIZE ");
		sql.append(" ,T2.UPLOAD_TIME,T2.UPLOAD_USERID,T2.UPLOAD_USERNAME ");
		sql.append(" FROM T_FM_SYS_BIZ_ATT T1, T_FM_SYS_ATTECHMENT T2  ");
		sql.append(" WHERE STATE='00' AND T1.ATT_ID = T2.ID ");
		sql.append(" AND T1.BIZ_ID = :BIZ_ID ");
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("BIZ_ID", bizId);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), parameters, new SysAttMapper());
	}

	@Override
	public Collection listAllAtt(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String gyh = params.optString("gyh");
		String username = params.optString("username");
		String currentRoleId = params.optString("currentRoleId");
		String fileName = params.optString("fileName");
		String comment = params.optString("comment");
		StringBuffer sql = new StringBuffer();
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		sql.append(" SELECT T.ID,T.UPLOAD_USERID,T.UPLOAD_USERNAME,T.CONTENT_TYPE,DATE_FORMAT(T.UPLOAD_TIME,'%Y-%m-%d %H:%i:%s') UPLOAD_TIME ");
		sql.append(" ,T.FILE_NAME,T.FTP_PATH,T3.COMMENT,T3.TOP_LEVEL ");
		sql.append(" FROM T_FM_SYS_ATTECHMENT T ,T_FM_SYS_BIZ_ATT T2  ,T_FM_ATT T3 ");
		sql.append(" WHERE 1=1 AND T.ID = T2.ATT_ID AND T2.BIZ_ID = T3.ID AND T2.STATE = '00' ");
		sql.append(" AND (T3.OPER_USER_ROLE_ID = :OPER_USER_ROLE_ID or T3.SHARE_ROLE_IDS LIKE '%"+currentRoleId+"%')");
		paramMap.put("OPER_USER_ROLE_ID", currentRoleId);
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND T.UPLOAD_TIME >= :startDate");
			paramMap.put("startDate", startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND T.UPLOAD_TIME <= :endDate");
			paramMap.put("endDate", endDate);
		}
		if(StringUtils.isNotEmpty(gyh)){
			sql.append(" AND T.UPLOAD_USERID LIKE '%"+gyh+"%' ");
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND T.UPLOAD_USERNAME LIKE '%"+username+"%' ");
		}
		if(StringUtils.isNotEmpty(fileName)){
			sql.append(" AND T.FILE_NAME LIKE '%"+fileName+"%' ");
		}
		if(StringUtils.isNotEmpty(comment)){
			sql.append(" AND T3.COMMENT LIKE '%"+comment+"%' ");
		}
		sql.append(" ORDER BY T3.TOP_LEVEL DESC,T.UPLOAD_TIME DESC,T.ID DESC ");
		sql.append(" LIMIT :START,:LIMIT");
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}
	
	
	
	@Override
	public int getAllAtt(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String gyh = params.optString("gyh");
		String username = params.optString("username");
		String currentRoleId = params.optString("currentRoleId");
		String fileName = params.optString("fileName");
		String comment = params.optString("comment");
		StringBuffer sql = new StringBuffer();
		Map<String,Object> paramMap = new HashMap<String,Object>();
		sql.append(" SELECT COUNT(*) CT FROM T_FM_SYS_ATTECHMENT T ");
		sql.append("  ,T_FM_SYS_BIZ_ATT T2  ,T_FM_ATT T3 ");
		sql.append(" WHERE 1=1 AND T.ID = T2.ATT_ID AND T2.BIZ_ID = T3.ID AND T2.STATE = '00' ");
		sql.append(" AND (T3.OPER_USER_ROLE_ID = :OPER_USER_ROLE_ID or T3.SHARE_ROLE_IDS LIKE '%"+currentRoleId+"%')");
		paramMap.put("OPER_USER_ROLE_ID", currentRoleId);
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND T.UPLOAD_TIME >= :startDate");
			paramMap.put("startDate", startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND T.UPLOAD_TIME <= :endDate");
			paramMap.put("endDate", endDate);
		}
		if(StringUtils.isNotEmpty(gyh)){
			sql.append(" AND T.UPLOAD_USERID LIKE '%"+gyh+"%' ");
//			paramMap.put("gyh","%"+gyh+"%");
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND T.UPLOAD_USERNAME LIKE '%"+username+"%' ");
//			paramMap.put("username","%"+username+"%");
		}
		if(StringUtils.isNotEmpty(fileName)){
			sql.append(" AND T.FILE_NAME LIKE '%"+fileName+"%' ");
		}
		if(StringUtils.isNotEmpty(comment)){
			sql.append(" AND T3.COMMENT LIKE '%"+comment+"%' ");
		}
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public SysAttechment getSysAttechment(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM T_FM_SYS_ATTECHMENT WHERE ID = :ID ");
		SqlParameterSource paramSource = new MapSqlParameterSource()
				.addValue("ID", id);
		return (SysAttechment) this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramSource, new SysAttMapper());
	}

	@Override
	public SysAttechment getSysAttechmentByFileName(String fileName) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void createBizAtt(SysBizAtt sysBizAtt) {
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO T_FM_SYS_BIZ_ATT(ID,BIZ_ID,ATT_ID,STATE) ");
		sql.append(" VALUES(UUID(),:BIZ_ID,:ATT_ID,'00') ");
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("BIZ_ID", sysBizAtt.getBizId())
				.addValue("ATT_ID", sysBizAtt.getAttId());
		this.getNamedParameterJdbcTemplate().update(sql.toString(), parameters);
	}

	
	@Override
	public void delAttByBizId(String bizId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE T_FM_SYS_BIZ_ATT ");
		sql.append(" SET STATE = '01' ");
		sql.append(" WHERE BIZ_ID = :BIZ_ID ");
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("BIZ_ID", bizId);
		this.getNamedParameterJdbcTemplate().update(sql.toString(), parameters);
	}

	@Override
	public void delAttByBizId(Long bizId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE T_FM_SYS_BIZ_ATT ");
		sql.append(" SET STATE = '01' ");
		sql.append(" WHERE BIZ_ID = :BIZ_ID ");
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("BIZ_ID", bizId);
		this.getNamedParameterJdbcTemplate().update(sql.toString(), parameters);
	}

	@Override
	public void delAttByBizId(Long bizId, String item) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void delAttByFileName(String fileName) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void createBizAtt(List list) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void delAttByBizId(List list) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Collection listAllAttByBizId(Long bizId, String item) {
//		StringBuffer sql = new StringBuffer();
//		sql.append(" SELECT T2.ID,T2.FTP_PATH,T2.CONTENT_TYPE,T2.FILE_NAME,T2.FILE_SIZE ");
//		sql.append(" ,T2.UPLOAD_TIME,T2.UPLOAD_USERID,T2.UPLOAD_USERNAME ");
//		sql.append(" FROM T_FM_SYS_BIZ_ATT T1, T_FM_SYS_ATTECHMENT T2  ");
//		sql.append(" WHERE T1.ATT_ID = T2.ID ");
//		sql.append(" AND T1.BIZ_ID = :BIZ_ID ");
//		SqlParameterSource parameters = new MapSqlParameterSource()
//				.addValue("BIZ_ID", bizId);
//		return this.getNamedParameterJdbcTemplate().query(sql.toString(), parameters, new SysAttMapper());
		return null;
	}

	@Override
	public void copySyaAttechment(Long bizId, Long beforeBizId) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Collection listIdsByNames(String fileName) {
		// TODO Auto-generated method stub
		return null;
	}}
